package jl.com.util;//package main.java.com.xray.power.spider.util;

import jl.com.aliyun.config.ConfigProperties;
import jl.com.entity.Article;
import jl.com.entity.WxNumber;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class JdbcDao {

    private PreparedStatement ptmt = null;
    private String name =  ConfigProperties.resource.getString("linux.xray_power.user");
    private String pwd = ConfigProperties.resource.getString("linux.xray_power.pass");

    private String ownName =  ConfigProperties.resource.getString("own.user");
    private String ownPwd = ConfigProperties.resource.getString("own.pwd");
    public JdbcDao() {
    }

    public Connection connectData(String username,String password)
    {
        Connection conn;
        try {
            Class.forName(ConfigProperties.resource.getString("driver"));
            String url = ConfigProperties.resource.getString("linux.xray_power.url");
            String ownUrl = ConfigProperties.resource.getString("own.url");
//            conn = DriverManager.getConnection(url, username,password);
            conn = DriverManager.getConnection(ownUrl, ownName,ownPwd);
            return conn;

        } catch (Exception e) {
            e.printStackTrace();
        }
		return null;
    }
    /**
     * 查询所有公众号信息
     */
    public  List<WxNumber>  selectAllCategoryMpService()throws SQLException{
        Connection conn= null ;
        Statement statement = null;
        ResultSet rs = null;

        String sql = "select * from t_wx_number";
        List<WxNumber> wxNumbers = new ArrayList<>();

        try {
            conn=connectData(name,pwd);
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                WxNumber wxNumber = new WxNumber();
                wxNumber.setId(rs.getInt("id"));
                wxNumber.setWxNumber(rs.getString("wx_number"));
                wxNumbers.add(wxNumber);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            rs.close();
            statement.close();
            conn.close();
        }
        return wxNumbers;
    }



    /**
     * 根据FirstTitle查询 article
     * @param firstTitle
     * @return
     * @throws SQLException
     */
    public Article  findContentIdByFirstTitle(String firstTitle)throws SQLException{
        Connection conn= null ;
        Statement statement = null;
        ResultSet resultSet = null;

        String sql = "select id from t_article where title = '"+firstTitle+"'";
        Article article = null;
        try {
            conn = connectData(name,pwd);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                article = new Article();
                article.setId(resultSet.getInt("id"));
            }
            resultSet.close();
        }catch (Exception e){
            resultSet.close();
            e.printStackTrace();
        }finally {
            statement.close();
            conn.close();
        }
        return article;
    }

    public  boolean insertArtice(Article article) throws SQLException
    {
        Connection conn= null ;
        PreparedStatement ps = null;

        String sql = "INSERT INTO `t_article`(article_id,title,cover,style,content,wx_number,createtime)" +
                "values(?,?,?,?,?,?,?)";
        try {
            conn=connectData(name,pwd);
             ps = conn.prepareStatement(sql);
            ps.setObject(1, article.getArticleId());
            ps.setObject(2, article.getTitle());
            ps.setObject(3, article.getCover());
            ps.setObject(4, article.getStyle());
            ps.setObject(5, article.getContent());
            ps.setObject(6, article.getWxNumber());
            ps.setObject(7, article.getCreateTime());
            int result = ps.executeUpdate();
            if (result>0){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ps.close();
            conn.close();
        }
        return false;
    }


    public boolean insertWxNumber(WxNumber wxNumber)throws SQLException{
        Connection conn= null ;
        PreparedStatement ps = null;

        String sql = " insert into t_wx_number(id,wx_number,createtime) values (?,?,?)";

        try {
            conn=connectData(name,pwd);
            ps = conn.prepareStatement(sql);
            ps.setObject(1, StringUtil.generateId());
            ps.setObject(2, wxNumber.getWxNumber());
            ps.setObject(3, new Date());
            int result = ps.executeUpdate();
            if (result>0){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ps.close();
            conn.close();
        }
        return  false;
    }

    /**
     * 查询所有文章信息
     * @return
     * @throws SQLException
     */
    public List< Article>  articleList()throws SQLException{
        Connection conn= null ;
        Statement statement = null;
        ResultSet resultSet = null;
        List< Article> articles = new ArrayList<>();
        String sql = "select * from t_article ";
        try {
            conn = connectData(name,pwd);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                Article article  = new Article();
                article.setId(resultSet.getInt("id"));
                article.setArticleId(resultSet.getInt("article_id"));
                article.setTitle(resultSet.getString("title"));
                article.setCover(resultSet.getString("cover"));
                article.setContent(resultSet.getString("content"));
                article.setWxNumber(resultSet.getString("wx_number"));
                article.setCreateTime(resultSet.getDate("createtime"));
                articles.add(article);
            }
            resultSet.close();
        }catch (Exception e){
            resultSet.close();
            e.printStackTrace();
        }finally {
            statement.close();
            conn.close();
        }
        return articles;
    }

    /**
     * 修改文章信息
     * @return
     * @throws SQLException
     */
    public boolean updateArticle(Article article)throws SQLException{
        Connection conn= null ;
        PreparedStatement ps = null;

        String sql = " update t_article set content = ? where id = ? ";

        try {
            conn=connectData(name,pwd);
            ps = conn.prepareStatement(sql);
            ps.setObject(1, article.getContent());
            ps.setObject(2, article.getId());
            int result = ps.executeUpdate();
            if (result>0){
                return true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ps.close();
            conn.close();
        }
        return  false;
    }

    //将文章内容src路径http 改成https
   /* JdbcDao jdbcDao = new JdbcDao();
    List<Article> articles =  jdbcDao.articleList();
    //        Article at = new Article();
//        at.setContent("<section style=\"margin-top: -60px;width: 100%;border-right: 10px solid rgb(255, 241, 241);box-sizing: border-box;\" data-width=\"100%\">\n" +
//                "           <img data-croporidata-src=\"https://mmbiz.qpic.cn/mmbiz_jpg/icNRZAHfALyns3tjib7UZpemgfugcSdR76X3kVg78Nb1PIMkXiblK9WM4XbIerScFkv0zLicQ3gTEGKwGQy3Rpb0ibg/640?wx_fmt=jpeg\" " +
//                "src=\"http://zhangsan-oss.oss-accelerate.aliyuncs.com/spider/1586930822153.jpg\" data-type=\"jpeg\" data-w=\"1080\" data-width=\"100%\" style=\"width: 514px;display: block;height: 334px;\">\n" +
//                "          <img data-croporidata-src=\"https://mmbiz.qpic.cn/mmbiz_jpg/icNRZAHfALyns3tjib7UZpemgfugcSdR76X3kVg78Nb1PIMkXiblK9WM4XbIerScFkv0zLicQ3gTEGKwGQy3Rpb0ibg/640?wx_fmt=jpeg\" \" +\n" +
//                "                \"src=\"http://zhangsan-oss.oss-accelerate.aliyuncs.com/spider/1586930822154.jpg\" data-type=\"jpeg\" data-w=\"1080\" data-width=\"100%\" style=\"width: 514px;display: block;height: 334px;\"> +\n" +
//                "                \"      </section>");
//        List<Article> articles =  new ArrayList<>();
//        articles.add(at);
    Article aty = new Article();
        for (Article article : articles) {
        List<String> strings = StringUtil.getImgStcList(article.getContent());
        for (String string : strings) {
            if (string.indexOf("https") < 0 ){
                article.setContent(article.getContent().replaceAll(string,string.replace("http","https")));
            }
            aty = article;
        }
        jdbcDao.updateArticle(aty);
        System.out.println("完成");*/
}